<?php
/**
 * Created by PhpStorm.
 * User: Korbin
 * Date: 2018/11/27
 * Time: 15:52
 */

namespace app\script\command;

use app\common\model\StatisticsReport;
use think\console\Input;
use think\console\Output;

class Report extends Base
{
    /**
     * 设置执行名称及配置传入参数、脚本描述
     */
    protected function configure()
    {
        $this->setName('report')
            ->setDescription('Run all the time');
    }

    /*********************公用验证部分************************************/
    /*
     * 执行脚本任务
     *
     * @param Input $input
     * @param Output $output
     */
    protected function execute(Input $input, Output $output)
    {
        $output->writeln('开始执行Video报表统计!'.date('Y-m-d H:i:s'));
        $reportModel = new StatisticsReport();
        $videoSql = <<<SQL
SELECT
	b.job_name,
	c.label,
	count(1) AS total,
	SUM(IF(a.thumb_num<=100,1,0)) AS '<=100',
	SUM(IF(a.thumb_num>=100,1,0)) AS '>=100',
	SUM(IF(a.thumb_num>=200,1,0)) AS '>=200',
	SUM(IF(a.thumb_num>=300,1,0)) AS '>=300',
	SUM(IF(a.thumb_num>=400,1,0)) AS '>=400',
	SUM(IF(a.thumb_num>=500,1,0)) AS '>=500',
	SUM(IF(a.thumb_num>=600,1,0)) AS '>=600',
	SUM(IF(a.thumb_num>=700,1,0)) AS '>=700',
	SUM(IF(a.thumb_num>=800,1,0)) AS '>=800',
	SUM(IF(a.thumb_num>=900,1,0)) AS '>=900',
	SUM(IF(a.thumb_num>=1000,1,0)) AS '>=1000',
	SUM(IF(a.thumb_num>=1200,1,0)) AS '>=1200',
	SUM(IF(a.thumb_num>=2000,1,0)) AS '>=2000',
	SUM(IF(a.thumb_num>=3000,1,0)) AS '>=3000',
	SUM(IF(a.thumb_num>=4000,1,0)) AS '>=4000',
	SUM(IF(a.thumb_num>=5000,1,0)) AS '>=5000',
	SUM(IF(a.thumb_num>=10000,1,0)) AS '>=10000',
	SUM(IF(a.thumb_num>=12000,1,0)) AS '>=12000'
FROM cap_data_video a
LEFT JOIN cap_job b ON a.job_id = b.id
LEFT JOIN sys_dict c ON b.lang = c.`value` AND c.type = 'caplang'
WHERE
	DATE_FORMAT(a.create_date,'%Y%m%d') = CURDATE()
GROUP BY a.job_id;
SQL;
        $videoInfo = $reportModel->query($videoSql);
        foreach ($videoInfo as $arr){
            $videoData['date'] = date('Ymd');
            $videoData['platform'] = $arr['job_name'];
            $videoData['type'] = 1;
            unset($arr['job_name']);
            unset($arr['label']);
            $insertData = [];
            foreach ($arr as $key=>$value){
                $videoData['report_type'] = $key;
                $videoData['report_value'] = $value;
                $insertData[] = $videoData;
            }
            $reportModel->isUpdate(false)->saveAll($insertData,false);
        }
        $output->writeln('Video报表统计执行完毕!'.date('Y-m-d H:i:s'));
        $output->writeln('开始执行News报表统计!'.date('Y-m-d H:i:s'));
        $newSql = <<<SQL
SELECT
	b.job_name,
	c.label,
	count(1) AS total,
	SUM(IF(a.like_count<=100,1,0)) AS '100',
	SUM(IF(a.like_count>=100,1,0)) AS '>=100',
	SUM(IF(a.like_count>=200,1,0)) AS '>=200',
	SUM(IF(a.like_count>=300,1,0)) AS '>=300',
	SUM(IF(a.like_count>=400,1,0)) AS '>=400',
	SUM(IF(a.like_count>=500,1,0)) AS '>=500',
	SUM(IF(a.like_count>=600,1,0)) AS '>=600',
	SUM(IF(a.like_count>=700,1,0)) AS '>=700',
	SUM(IF(a.like_count>=800,1,0)) AS '>=800',
	SUM(IF(a.like_count>=900,1,0)) AS '>=900',
	SUM(IF(a.like_count>=1000,1,0)) AS '>=1000',
	SUM(IF(a.like_count>=2000,1,0)) AS '>=2000',
	SUM(IF(a.like_count>=3000,1,0)) AS '>=3000',
	SUM(IF(a.like_count>=4000,1,0)) AS '4000以上'
FROM cap_news a
LEFT JOIN cap_job b ON a.job_id = b.id
LEFT JOIN sys_dict c ON b.lang = c.`value` AND c.type = 'caplang'
WHERE
	DATE_FORMAT(a.create_date,'%Y%m%d') = CURDATE()
GROUP BY job_id;
SQL;
        $newsInfo = $reportModel->query($newSql);
        foreach ($newsInfo as $newArr){
            $newsData['date'] = date('Ymd');
            $newsData['platform'] = $newArr['job_name'];
            $newsData['type'] = 2;
            unset($newArr['job_name']);
            unset($newArr['label']);
            $insertData = [];
            foreach ($newArr as $key=>$value){
                $newsData['report_type'] = $key;
                $newsData['report_value'] = $value;
                $insertData[] = $newsData;
            }
            $reportModel->isUpdate(false)->saveAll($insertData,false);
        }
        $output->writeln('News报表统计执行完毕!'.date('Y-m-d H:i:s'));
    }
}